﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Threading;
using System.Threading.Tasks;
using System.Collections.Concurrent;

class CFlightDB
{
    CConnectionManager m_ConnectionManager;
    SqlCommand m_SQLCommand;
    SqlConnection m_SQLConnection;

    public CFlightDB()
    {
        m_ConnectionManager = new CConnectionManager();
        m_SQLConnection = new SqlConnection("Data Source = 10.5.5.234; Initial Catalog = FlightDB; USER ID = admin; PASSWORD = 123; Connection Timeout = 2147483647");
        m_SQLCommand = new SqlCommand("", m_SQLConnection);
        m_SQLCommand.CommandTimeout = 600;
    }

    public List<CPair<int, String>> GetAllRawFlight()
    {
        m_SQLConnection.Open();
        m_SQLCommand.CommandText = "EXECUTE FlightIdFromRawVal";
        SqlDataReader SQLReader = m_SQLCommand.ExecuteReader();
        List<int> IdList = new List<int>();

        while (SQLReader.Read())
        {
            int nId = SQLReader.GetInt32(0);
            IdList.Add(nId);
        }
        SQLReader.Close();

        List<CPair<int, String>> FlightList = new List<CPair<int, String>>();
        foreach (int nFlightId in IdList)
        {
            m_SQLCommand.CommandText = String.Format("SELECT Name FROM Flight WHERE Id = {0}", nFlightId);
            SQLReader = m_SQLCommand.ExecuteReader();
            SQLReader.Read();

            CPair<int, String> Pair = new CPair<int, String>();
            Pair.m_First = nFlightId;
            Pair.m_Second = SQLReader[0].ToString();
            FlightList.Add(Pair);
            SQLReader.Close();
        }

        m_SQLConnection.Close();

        return FlightList;
    }

    public List<CProcessedFlight> GetAllProcessedFlight()
    {
        List<CProcessedFlight> FlightList = new List<CProcessedFlight>();

        m_SQLConnection.Open();
        m_SQLCommand.CommandText = "SELECT Id FROM ProcessedFlight";
        SqlDataReader SQLReader = m_SQLCommand.ExecuteReader();
        List<int> IdList = new List<int>();

        while (SQLReader.Read())
        {
            int nId = SQLReader.GetInt32(0);
            IdList.Add(nId);
        }
        SQLReader.Close();

        foreach (int nId in IdList)
        {
            m_SQLCommand.CommandText = String.Format("SELECT FlightID FROM ProcessedFlight WHERE Id = {0}", nId);
            SQLReader = m_SQLCommand.ExecuteReader();
            SQLReader.Read();
            int nFlightId = SQLReader.GetInt32(0);
            SQLReader.Close();

            m_SQLCommand.CommandText = String.Format("SELECT Name FROM Flight WHERE Id = {0}", nFlightId);
            SQLReader = m_SQLCommand.ExecuteReader();
            SQLReader.Read();
            String sFlightName = SQLReader[0].ToString();
            SQLReader.Close();

            m_SQLCommand.CommandText = String.Format("SELECT Freq FROM ProcessedFlight WHERE Id = {0}", nId);
            SQLReader = m_SQLCommand.ExecuteReader();
            SQLReader.Read();
            double fFreq = double.Parse(SQLReader[0].ToString());
            SQLReader.Close();

            FlightList.Add(new CProcessedFlight(nFlightId, sFlightName, fFreq));
        }

        return FlightList;
    }

    public void ConvertRawFlight(List<int> RawFlightList)
    {
        int nFreq = 5; //единая частота
        bool Fail = false;

        m_SQLConnection.Open();

        foreach (int nFlightId in RawFlightList)
        {
            //выборка Id сырых параметров для полёта
            m_SQLCommand.CommandText = String.Format("SELECT ParamId FROM RawValue WHERE FlightId = {0} GROUP BY ParamId ORDER BY ParamId", nFlightId);
            SqlDataReader SQLReader = m_SQLCommand.ExecuteReader();
            List<int> ParamIdList = new List<int>();

            while (SQLReader.Read())
            {
                int nParamId = SQLReader.GetInt32(0);
                ParamIdList.Add(nParamId);
            }
            SQLReader.Close();

            int[] RegVecIds = null;
            bool RegVecInsert = false;
            int nProcessedFlightId = CheckProcessedFlight(nFlightId, nFreq, 0.25F);

            foreach (int nParamId in ParamIdList)
            {
                Console.WriteLine("Processing parameter #{0}...", nParamId);
                List<float> ConvertedValues = new List<float>();

                try
                {
                    ConvertedValues = ConvertValues(nFlightId, nParamId, nFreq); //конвертация параметра
                }
                catch(Exception e)
                {
                    if (e.Message == "Tacts are missed")
                    {
                        Fail = true;
                    }
                    else
                    {
                        throw (e);
                    }
                }
                if (ConvertedValues.Count > 0)
                {
                    if (!RegVecInsert)
                    {
                        RegVecInsert = true;
                        RegVecIds = InsertAllRegVecs(ConvertedValues.Count, nProcessedFlightId, nFreq); 
                    }
                    AddConvertedParameter(ConvertedValues, nFlightId, nParamId, nFreq, RegVecIds); // заливка в БД 
                    Console.WriteLine("  -> {0} parameter values were added", ConvertedValues.Count());
                }
                else
                {
                    Console.WriteLine("  -> Parameter is empty");
                }
            }
            // Добавление времени
            Console.WriteLine("Processing parameter #{0}...", "1131 (Time)");
            List<float> TimeValues = new List<float>();

            for (int i = 0; i < RegVecIds.Length; ++i)
            {
                TimeValues.Add((float)1.0 / nFreq * i);
            }
            AddConvertedParameter(TimeValues, nFlightId, 1131, nFreq, RegVecIds);
            Console.WriteLine("  -> {0} parameter values were added", TimeValues.Count());
        }

        if (Fail)
        {
            throw (new Exception("Fail"));
        }

        m_SQLConnection.Close();
    }

    private List<float> ConvertValues(int nFlightId, int nParamId, int nFreq)
    {
        List<float> ConvertedVal = new List<float>();

        m_SQLCommand.CommandText = String.Format("SELECT Freq FROM Parameter WHERE Id = {0}", nParamId);
        SqlDataReader SQLReader = m_SQLCommand.ExecuteReader();
        SQLReader.Read();
        float fRawFreq = float.Parse(SQLReader[0].ToString());
        SQLReader.Close();

        // Проверим, заполнено ли более 3/4 значений в RawValue
        m_SQLCommand.CommandText = String.Format("EXECUTE IsParameterRegistered @FlightId = {0}, @ParamId = {1}", nFlightId, nParamId);
        SQLReader = m_SQLCommand.ExecuteReader();
        SQLReader.Read();

        bool ParameterIsRegistered = (SQLReader.GetInt32(0) == 1);

        List<float> RawValue = new List<float>();
        if (ParameterIsRegistered == false) // Это должно стоять здесь, чтобы не делать бессмысленных телодвижений!
        {
            SQLReader.Close();
            return ConvertedVal;
        }

        SQLReader.Close();

        m_SQLCommand.CommandText = String.Format("SELECT Freq FROM Parameter WHERE Id = {0}", nParamId);
        SQLReader = m_SQLCommand.ExecuteReader();
        SQLReader.Read();

        float ParameterFreq = (float)SQLReader.GetDouble(0);

        SQLReader.Close();

        //Взятие значений (тут есть изменение для учета дублированных(?!) записей)
        m_SQLCommand.CommandText = String.Format("SELECT MIN(Val), Tact FROM RawValue WHERE FlightId = {0} AND ParamId = {1} GROUP BY Tact, regTime ORDER BY regTime",
                                                                                                          nFlightId, nParamId);

        SQLReader = m_SQLCommand.ExecuteReader();

        //Проверка первой регистрации. Ты говорил, что параметры, которые не регестрируются с начала нам не нужны
        //SQLReader.Read();
        //float fVal = (float)SQLReader.GetDouble(0);

        float fVal = 0.0F;
        int CurrentTact = 0, Counter32 = 0;

        // Здесь нужна хранимая процедура, которая вычисляет, заполнено ли более 3/4 значений в RawValue
        //if (Math.Abs(fVal - 2.2e+09) < float.Epsilon) // возможно это быдло код, но как по-другому сравнить не придумал )
        bool TactsAreMissed = false;

        while (SQLReader.Read())
        {
            fVal = (float)SQLReader.GetDouble(0);
            CurrentTact = SQLReader.GetInt32(1);

            if ((CurrentTact * ParameterFreq - Counter32) > float.Epsilon)
            {
                System.IO.StreamWriter Log = new System.IO.StreamWriter("ConvertorRaw2Vector ErrorLog.txt", true);

                Log.WriteLine("***********");
                Log.WriteLine("Converting flight {0}, parameter {1}. Some tacts are missing... Consider tact {2}.", nFlightId, nParamId, CurrentTact);
                Log.Close();

                TactsAreMissed = true;

                while ((CurrentTact * ParameterFreq - Counter32) > float.Epsilon)
                {
                    Counter32 += 32;
                }
            }
            Counter32 += 32;

            RawValue.Add(fVal);
        }
        SQLReader.Close();

        if (TactsAreMissed)
        {
            throw (new Exception("Tacts are missed"));
        }
        
        CConvertor Convertor = new CConvertor(fRawFreq, nFreq); // здесь происходит линейная интерполяция
        return Convertor.ConvertRawValues(RawValue);
    }

    private void AddConvertedParameter(List<float> Values, int nFlightId, int nParamId, int nFreq, int[] RegVecIds)
    {
        float minFreq = 0.25F;

        m_SQLCommand.CommandText = String.Format("SELECT Name FROM Parameter WHERE Id = {0}", nParamId);
        SqlDataReader SQLReader = m_SQLCommand.ExecuteReader();
        SQLReader.Read();
        String sName = SQLReader[0].ToString();
        SQLReader.Close();

        //m_SQLCommand.CommandText = String.Format("SELECT MAX(Number) FROM Parameter");
        //SQLReader = m_SQLCommand.ExecuteReader();
        //SQLReader.Read();
        //int nParamNum = SQLReader.GetInt32(0) + 1;
        //SQLReader.Close();
        
        //добавление в processedflight

        int nProcessedFlightId = CheckProcessedFlight(nFlightId, nFreq, minFreq);
        SQLReader.Close();

        //Добавление параметра
        //m_SQLCommand.CommandText = String.Format("INSERT Parameter VALUES('{0}', {1})", sName , nParamNum);
        //m_SQLCommand.ExecuteNonQuery();

        //m_SQLCommand.CommandText = String.Format("SELECT Id FROM Parameter WHERE Number = {0}", nParamNum);
        //SQLReader = m_SQLCommand.ExecuteReader();
        //SQLReader.Read();
        //int nParamId = SQLReader.GetInt32(0);
        //SQLReader.Close();
        //int[] RegVecIds = InsertAllRegVecs(Values.Count(), nProcessedFlightId, nFreq);

        
        ParallelOptions Options = new ParallelOptions();
        Options.MaxDegreeOfParallelism = 16;
        Parallel.ForEach(Partitioner.Create(0, Values.Count), range =>
        {
            for (int nInd = range.Item1; nInd < range.Item2; nInd++)
            {
                float fVal = Values[nInd];
                float fRegTime = (float)1.0 / nFreq * nInd;
                int nTact = nInd + 1;
                int nThreadId = Thread.CurrentThread.ManagedThreadId;
                SqlCommand SQLCommand = m_ConnectionManager.GetSqlCommand(nThreadId);

                //int nRegVecId = InsertRegVec(fRegTime, nTact, nProcessedFlightId, SQLCommand);
                int nRegVecId = RegVecIds[nInd];
                InsertValue(nRegVecId, nParamId, fVal, nProcessedFlightId, nTact, SQLCommand);
            }
        }
        );

        m_ConnectionManager.CloseConnections(); 

    }

    private int InsertRegVec(float fRegTime, int nTact, int nProcessedFlightId, SqlCommand SQLCommand)
    {
        String sRegTime = fRegTime.ToString().Replace(",", ".");
        //SQLCommand.CommandText = String.Format("IF NOT EXISTS (SELECT Id FROM RegVec WHERE (Tact = {0} AND regTime = {1} AND ProcessedFlightId ={2}))" +
        //                                       "BEGIN INSERT INTO RegVec (Tact, regTime, ProcessedFlightId) VALUES({0}, {1}, {2}) END", nTact
        //                                                                                                                 , sRegTime
        //                                                                                                                 , nProcessedFlightId);
        SQLCommand.CommandText = String.Format("INSERT INTO RegVec (Tact, regTime, ProcessedFlightId) VALUES({0}, {1}, {2})", nTact
                                                                                                                         , sRegTime
                                                                                                                         , nProcessedFlightId);
        SQLCommand.ExecuteNonQuery();

        SQLCommand.CommandText = String.Format("SELECT Id FROM RegVec WHERE (Tact = {0} AND regTime = {1} AND ProcessedFlightId ={2})", nTact
                                                                                                                         , sRegTime
                                                                                                                         , nProcessedFlightId);

        SqlDataReader SQLReader = SQLCommand.ExecuteReader();
        SQLReader.Read();
        int nRegVecId = SQLReader.GetInt32(0);
        SQLReader.Close();

        return nRegVecId;
    }

    private int[] InsertAllRegVecs(int nTacts, int nProcessedFlightId, float nFreqs)
    {
        int[] RegVecIds = new int[nTacts];
        m_SQLCommand.CommandText = String.Format("EXECUTE InsertRegVecs {0}, {1}, {2}", nProcessedFlightId, nFreqs, nTacts);
        SqlDataReader SQLReader = m_SQLCommand.ExecuteReader();

        for (int i = 0; i < nTacts; ++i)
        {
            if (SQLReader.Read())
            {
                RegVecIds[i] = SQLReader.GetInt32(0);
            }
            else
            {
                RegVecIds[i] = 0;
            }
        }
        SQLReader.Close();
        return RegVecIds;
    }

    private void InsertValue(int nRegVecId, int nParamId, float fVal, int nProcessedFlightId, int nTact, SqlCommand SQLCommand)
    {
        String sVal = fVal.ToString().Replace(",", ".");
        SQLCommand.CommandText = String.Format("INSERT INTO Value(VecId, ParamId, Val, ProcessedFlightId, Tact) VALUES({0}, {1}, {2}, {3}, {4})", nRegVecId
                                                                                                  , nParamId
                                                                                                  , sVal
                                                                                                  , nProcessedFlightId
                                                                                                  , nTact);
        SQLCommand.ExecuteNonQuery();
    }

    private int CheckProcessedFlight(int nFlightId, int nFreq, float minFreq)
    {
        m_SQLCommand.CommandText = String.Format("IF NOT EXISTS (SELECT * FROM ProcessedFlight WHERE (FlightID = {0} AND Freq = {1}))" +
                                         "BEGIN INSERT ProcessedFlight VALUES({0}, {1}) END", nFlightId, (int)(nFreq / minFreq));

        m_SQLCommand.ExecuteNonQuery();

        //Взятие ProcessedFlightId
        m_SQLCommand.CommandText = String.Format("SELECT Id FROM ProcessedFlight WHERE (FlightID = {0} AND Freq = {1})", nFlightId, nFreq / minFreq);
        SqlDataReader SQLReader = m_SQLCommand.ExecuteReader();
        int nProcessedFlightId = -1;
        if (SQLReader.Read())
        {
            nProcessedFlightId = SQLReader.GetInt32(0);
        }
        SQLReader.Close();
        return nProcessedFlightId;
    }
}

